﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace System.Data.SQLite
{
    public partial class SQLiteHelper
    {
        private System.Text.RegularExpressions.Regex dateReg = new Text.RegularExpressions.Regex(@"[1,2][0-9]{3}/\d{1,2}/\d{1,2}");

        public Tuple<bool, Exception> Insert(string tableName, DataTable data)
        {
            StringBuilder sbCol = new System.Text.StringBuilder();
            StringBuilder sbVal = new System.Text.StringBuilder();

            try
            {
                BeginTransaction();
                foreach (DataRow row in data.Rows)
                {
                    sbCol.Append("insert into ");
                    sbCol.Append(tableName);
                    sbVal.Append(" values(");
                    sbVal.Append(string.Join(",", row.ItemArray.Select(x => GetValueString(x)).ToArray()));
                    sbVal.Append(");");

                    cmd.CommandText = sbCol.ToString() + sbVal.ToString();
                    cmd.ExecuteNonQuery();
                    sbCol.Clear();
                    sbVal.Clear();
                }
                Commit();
                return new Tuple<bool, Exception>(true, null);
            }
            catch (Exception ex)
            {
                Rollback();
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        private string GetValueString(object col)
        {
            var type = col.GetType();
            switch (type.Name)
            {
                case "Double": return col.ToString();
                case "DateTime":return LiteQueryWPF.Common.DateHelper.DateToTimestamp(col.ToString()).ToString();
                case "String": return "'" + col.ToString() + "'";
                default: return "'" + col.ToString() + "'";
            }
        }

    }
}
